package site.assad.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import site.assad.domain.Game;
import site.assad.domain.User;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Author: Al-assad 余林颖
 * E-mail: yulinying_1994@outlook.com
 * Date: 2017/12/15 11:03
 * Description:
 */
@Repository
public class UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    //验证 user 是否存在
    public boolean checkUserById(final int userid){
        final String sql = "SELECT COUNT(user_id) " +
                "FROM users " +
                "WHERE user_id = ?";
        int result = jdbcTemplate.queryForObject(sql,new Object[]{userid},Integer.class);
        return result > 0;
    }


    //collect game
    public void collectGame(final User user, final Game game){
        final String sql = "insert into games_collect(game_id,user_id) " +
                "values(?,?)";
        jdbcTemplate.update(sql,new Object[]{user.getId(),game.getId()});
    }


    //获取 User 相关信息
    public User getUserById(final int userid){
        final String sql = "SELECT (user_id,user_name,user_password,user_icon " +
                "FROM users WHERE user_id = ?";

        User user = new User();

        jdbcTemplate.query(sql,new Object[]{userid}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                user.setId(rs.getInt("user_id"));
                user.setName(rs.getString("user_name"));
                user.setPassword(rs.getString("user_password"));
                user.setIcon(rs.getString("user_icon"));
            }
        });

        //获取并设置 games 属性
        final String sql2 = "SELECT (games_item.game_id,games_item.game_name,games_item.game_collect) " +
                "FROM games_item " +
                "WHERE games_item.game_id = (SELECT games_collect.game_id WHERE games_collect.user_id = ?)";

        List<Game> gameList = null;
        gameList = jdbcTemplate.query(sql2, new Object[]{userid}, new RowMapper<Game>() {
            @Override
            public Game mapRow(ResultSet rs, int rowNum) throws SQLException {
                Game game = new Game();
                game.setId(rs.getInt("games_item.game_id"));
                game.setName(rs.getString("games_item.game_name"));
                game.setCollectedCount(rs.getInt("games_item.collected_count"));
                return game;
            }
        });
        user.setGames(gameList);

        return user;
    }




}
